# -*- coding: utf-8 -*-
"""
Created on Sun Aug 25 11:28:36 2024

@author: jzz
"""

import pandas as pd
import numpy as np
import matplotlib.pyplot as plt
import scipy.stats as stats
import os
import database_new_student as database_new
import copy
from dateutil.relativedelta import relativedelta
import datetime
import statsmodels.api as sm
import warnings
from scipy.stats import ttest_ind
from scipy.stats import f_oneway
import sys
warnings.filterwarnings("ignore")
plt.style.use('seaborn-v0_8')  # plt.style.use('dark_background') 
plt.rcParams['axes.unicode_minus'] = False  # 用来正常显示负号
plt.rcParams['font.sans-serif']=['SimHei'] #用来正常显示中文标签

def merge_direction(row):
    if row['2021-2024方向选择'] == '正' and row['全样本方向选择'] == '正':
        return '正'
    elif row['2021-2024方向选择'] == '负' and row['全样本方向选择'] == '负':
        return '负'
    else:
        return '舍去'

'''
def merge_direction(row):
    return row['2015-2021方向选择']
'''
def get_stock_score_pos(df_percent,a1,a2):#计算正向线性仓位
    score_stocks= df_percent.copy()
    length=len(df_percent)
    for i in range(0, length):
        x=df_percent.iloc[i,0]
        if x<a1:
            score_stock=0
        elif x<a2:
            score_stock=1/(a2-a1)*(x-a1)
        else:
            score_stock=1
        score_stocks.iloc[i,0] = score_stock
    return score_stocks  

def backtest_stock(table,score):
    table = pd.concat([table,score],axis=1).sort_index().dropna()#拼接2个dataframe，按index排序，删除缺失值 
    table.columns = ['行业指数','万得全A','行业指数收益率','万得全A收益率','择时指标得分']
    table['是否实际持有'] = table['择时指标得分'].shift(1)
    table['策略收益率'] = table['是否实际持有']*table['行业指数收益率']+(1-table['是否实际持有'])*table['万得全A收益率']
    table.loc[table.index[0],'策略收益率'] = 0 
    
    table['策略相对万得全A超额收益率']=table['策略收益率']-table['万得全A收益率']
    table['策略相对行业指数超额收益率']=table['策略收益率']-table['行业指数收益率']
    table['策略净值'] = (1+table['策略收益率']).cumprod()  #累乘

    table['行业指数净值']=table['行业指数']/table.loc[table.index[0],'行业指数']
    table['策略相对行业指数净值']=table['策略净值']/table['行业指数净值']
    table['万得全A净值']=table['万得全A']/table.loc[table.index[0],'万得全A']
    table['策略相对万得全A净值']=table['策略净值']/table['万得全A净值']
    table['策略是否绝对获胜'] = (table['策略收益率']>0)
    table['策略是否相对万得全A获胜'] = (table['策略收益率']-table['万得全A收益率'])>0
    table['策略是否相对行业指数获胜'] = (table['策略收益率']-table['行业指数收益率'])>0
    absolute = table['策略是否绝对获胜'].sum()/table['策略是否绝对获胜'].count()
    relative_A = table['策略是否相对万得全A获胜'].sum()/table['策略是否相对万得全A获胜'].count()
    relative_industry = table['策略是否相对行业指数获胜'].sum()/table['策略是否相对行业指数获胜'].count()
    _,_, sharpe_ratio, _, max_drawdown, _, _ = Analyze.evaluate_indicator(table['策略净值'], 12, 0)
    
    len_nav = table.shape[0]
    annual_yield = (table['策略净值'].iloc[-1]/table['策略净值'].iloc[0])**(12/len_nav)-1
    annual_yieldindustry=(table['行业指数净值'].iloc[-1]/table['行业指数净值'].iloc[0])**(12/len_nav)-1
    annual_yieldA=(table['万得全A净值'].iloc[-1]/table['万得全A净值'].iloc[0])**(12/len_nav)-1
    annual_yield_industry=annual_yield-annual_yieldindustry
    annual_yield_A=annual_yield-annual_yieldA
    return table,annual_yield,annual_yieldindustry, annual_yieldA, annual_yield_A,annual_yield_industry,absolute, relative_A, relative_industry,sharpe_ratio, max_drawdown



def get_stock_score_neg(df_percent,a1,a2):#计算负向线性仓位
    score_stocks= df_percent.copy()
    length=len(df_percent)
    for i in range(0, length):
        x=df_percent.iloc[i,0]
        if x<a1:
            score_stock=1
        elif x<a2:
            score_stock=1/(a1-a2)*(x-a2)
        else:
            score_stock=0
        score_stocks.iloc[i,0] = score_stock
    return score_stocks  

def get_stock_score(df_percent,a1,a2,direction='正'):
    if direction=='正':
        return get_stock_score_pos(df_percent,a1,a2)
    elif direction=='负':
        return get_stock_score_neg(df_percent,a1,a2)
    
def hpfilter(table,lamb=5):#滤波函数
        '''
        获取每列的hp滤波后的趋势项
        table:pd.DataFrame或pd.Series
        
        '''
        is_series = isinstance(table, pd.Series)
        if is_series:
            table = pd.DataFrame(table)
            
        result = pd.DataFrame(index=table.index,columns=table.columns)
        for i in range(result.shape[1]):
            not_nan_data = table.iloc[:,i].dropna() 
            if len(not_nan_data)<2: # 至少要三个数才能做HP滤波
                continue 
            _,not_nan_data_hp = sm.tsa.filters.hpfilter(not_nan_data,lamb) 
            # 即使数据是间隔为nan也可处理，但有间隔nan如果太多，使用同一lambda可能会有误差
            result.iloc[:,i].loc[not_nan_data_hp.index] = not_nan_data_hp 
        
        if is_series:
            result = result.iloc[:,0] 
            
        return result.astype('float32') 

class Analyze:
    '''计算指标或画图进行分析''' 

    def evaluate_indicator(net_value,tradeday_per_year,anual_riskfree_rate):
        '''
        已知净值曲线之后进行各类指标的计算
    
        Parameters
        ----------
        net_value : pd.Series 
            时间序列.
        anual_riskfree_rate : float
            年化无风险利率.
        tradeday_per_year：如果净值序列是月度的，那就取12；如果是日度的，就取250（每年的交易日数据）；如果是周度的，就取52（每年的周数）
    
        Returns
        -------
        annual_yield：年化收益率
        annual_std：每期收益率的年化标准差
        sharpe_ratio：年化收益率的夏普比率
        drawdown：回撤曲线
        max_drawdown：最大回撤
    
        '''
        net_value0 = copy.copy(net_value)
        
        net_value = np.array(net_value)
        len_nav = len(net_value)
        annual_yield = (net_value[-1]/net_value[0])**(tradeday_per_year/len_nav)-1
        
        yield_of_nav = net_value[1:]/net_value[0:-1]-1
        annual_std = np.std(yield_of_nav)*np.sqrt(tradeday_per_year)
        
        sharpe_ratio = (annual_yield-anual_riskfree_rate)/annual_std
        
        max_nv = net_value0.cummax() 
        drawdown = (net_value0 - max_nv) / max_nv
        max_drawdown = np.max(-drawdown)
        
        # 分年度收益
        yield_year_last = net_value0.resample('y').last().pct_change() 
        yield_year_first_to_last = net_value0.resample('y').last()/net_value0.resample('y').first()-1
        
        return annual_yield,annual_std,sharpe_ratio,drawdown,max_drawdown,yield_year_last,yield_year_first_to_last 
    
    def evaluate_indicator_multi_asset(net_value,tradeday_per_year,anual_riskfree_rate):
        '''
        和evaluate_indicator区别是这里net_value是多资产的净值序列组成的dataframe

        Parameters
        ----------
        net_value : DataFrame
            DESCRIPTION.
        tradeday_per_year：如果净值序列是月度的，那就取12；如果是日度的，就取250（每年的交易日数据）；如果是周度的，就取52（每年的周数）
        anual_riskfree_rate : float
            0 or 0.03 ...

        Returns
        -------
        indicators : DataFrame
            DESCRIPTION.
        drawdowns : DataFrame
            DESCRIPTION.
        yield_year_lasts : DataFrame
            DESCRIPTION.
        yield_year_first_to_lasts : DataFrame
            DESCRIPTION.

        ''' 
        indicators = pd.DataFrame(index=net_value.columns,columns=['年化收益率','年化波动率','夏普比率','最大回撤'])
        drawdowns = [] 
        yield_year_lasts = [] 
        yield_year_first_to_lasts = [] 
        
        for code in net_value.columns:
            cur_nv = net_value[code]
            annual_yield,annual_std,sharpe_ratio,drawdown,max_drawdown,yield_year_last,yield_year_first_to_last = \
                Analyze.evaluate_indicator(cur_nv,tradeday_per_year,anual_riskfree_rate) 
            indicators.loc[code,['年化收益率','年化波动率','夏普比率','最大回撤']] = [annual_yield,annual_std,sharpe_ratio,max_drawdown]
            
            drawdowns.append(drawdown) 
            yield_year_lasts.append(yield_year_last) 
            yield_year_first_to_lasts.append(yield_year_first_to_last) 
        
        drawdowns = pd.concat(drawdowns,axis=1); 
        yield_year_lasts = pd.concat(yield_year_lasts,axis=1)
        yield_year_first_to_lasts = pd.concat(yield_year_first_to_lasts,axis=1)
        
        return indicators,drawdowns,yield_year_lasts,yield_year_first_to_lasts

'''
def get_rolling_winrate(table_best, start_date, end_date):
    start_date = pd.to_datetime(start_date)
    end_date = pd.to_datetime(end_date)
    
    df = pd.DataFrame(index=pd.date_range(start_date, end_date, freq='M'))
    df['滚动6个月胜率'] = np.nan
    
    for i in range(len(df)):
        # 获取前6个数据，包括当前月份
        table = table_best['策略是否相对行业指数获胜'].iloc[i:i]
        
        if len(table) == 0:  # 确保有6个月的数据
            df.iloc[i, df.columns.get_loc('滚动6个月胜率')] = 1
    
    return df
    
'''

def get_percentile(windows, df, start_date, end_date):
    #计算历史分位数,输入窗口、数据、起始和结束时间
    df_percent = df.copy()
    df_percent=df_percent.truncate(start_date,end_date)
    start_loc = df.index.get_loc(start_date)
    end_loc = df.index.get_loc(end_date)
    for i in range(start_loc, end_loc+1):
        df_before_i = df[:i+1]
        if len(df_before_i)>=windows*12:#历史数据超过windows
            pre = df.iloc[i-windows*12+1:i+1,0]
            df_percent.iloc[i-start_loc]=stats.percentileofscore(pre, df.iloc[i,0])/100
        elif len(df_before_i)>=windows_min:#历史数据超过windows_min但不到windows_max
            pre = df.iloc[0:i+1,0]
            df_percent.iloc[i-start_loc]= stats.percentileofscore(pre, df.iloc[i,0])/100
        else:
            df_percent.iloc[i-start_loc]=np.nan
    return df_percent

class Fs:
    '''用于财报的工具函数'''
    
    def calc_TTM(table):
        '''计算TTM，输入的必须为标准化后的财务表格 
           要求输入：table的columns为股票代码,index为datetimeIndex'''
        dates = pd.date_range(table.index[0],table.index[-1],freq='q') 
        table = table.reindex(dates) # 将表格标准化成index为每个季度
        
        table_ttm = pd.DataFrame(index=table.index,columns=table.columns)
        for i in range(3,table.shape[0]):
            cur_date = table.index[i]
            if cur_date.month==12:
                table_ttm.iloc[i,:] = copy.copy(table.iloc[i,:])
            elif cur_date.month==3:
                table_ttm.iloc[i,:] = copy.copy(table.iloc[i,:]+(table.iloc[i-1,:]-table.iloc[i-4,:]))
            elif cur_date.month==6:
                table_ttm.iloc[i,:] = copy.copy(table.iloc[i,:]+(table.iloc[i-2,:]-table.iloc[i-4,:]))
            elif cur_date.month==9:
                table_ttm.iloc[i,:] = copy.copy(table.iloc[i,:]+(table.iloc[i-3,:]-table.iloc[i-4,:]))
        return table_ttm 
    
    def calc_single_quarter(table):
        '''计算单季度指标，输入的必须为标准化后的财务表格
        要求输入：columns为股票代码
        index为datetime'''
        dates = pd.date_range(table.index[0],table.index[-1],freq='q') 
        table = table.reindex(dates) # 将表格标准化成index为每个季度
        
        table_single = pd.DataFrame(index=table.index,columns=table.columns)
        for i in range(1,table.shape[0]):
            cur_date = table.index[i]
            if cur_date.month==3:
                table_single.iloc[i,:] = copy.copy(table.iloc[i,:])
            else: 
                table_single.iloc[i,:] = copy.copy(table.iloc[i,:]-table.iloc[i-1,:])
        return table_single 
    
    def calc_avg(table,window=5,min_periods=1):
        '''计算存量的window期均值
        如果为nan，则该期不计入平均
        要求输入：columns为股票代码
        index为datetime'''    
        
        dates = pd.date_range(table.index[0],table.index[-1],freq='q') 
        table = table.reindex(dates) # 将表格标准化成index为每个季度
        
        # 注意这里的min_periods=1可以使rolling函数只取一个数据点来做平均，窗口内至少有一个数据点即可
        table_avg = table.rolling(window,min_periods=min_periods).mean()
        return table_avg 

    def to_month_data(df):
        '''将财报数据reindex成均匀的月频''' 
        new_index = pd.date_range(df.index[0],df.index[-1],freq='m') 
        df = df.reindex(new_index) 
        return df 
    
    def calc_yoy(df):
        '''计算同比''' 
        df2 = Fs.to_month_data(df) 
        df_yoy = df2/df2.shift(12)-1 
        return df_yoy 

    def get_next_n_month_last_date(cur_date,n):
        '''获取当天的下n个月的最后一天'''
        next_month = cur_date + relativedelta(months=n+1) 
        next_month = next_month.replace(day=1) 
        next_month_last_date = next_month-datetime.timedelta(days=1)
        return next_month_last_date 

    def deal_inf(df):
        '''处理inf或者-inf值，将其替换成np.nan''' 
        df = df.replace({np.inf:np.nan,-np.inf:np.nan}) 
        return df 
    
def single_deal(data_xianxing,shift_num,need_yoy,is_w_d):
        '''对单指标数据进行滞后、同比和降频等处理
        is_w_d是指是否周、日频数据'''
        if is_w_d: # 原数据是周、日频，需要降成月频
            data_xianxing = data_xianxing.resample('m').mean() 
        else:
            data_xianxing = data_xianxing.resample('m').last()
        if need_yoy: # 原数据是当月值，需要处理成当月同比
            data_xianxing = Fs.calc_yoy(data_xianxing)
        data_xianxing0 = copy.copy(data_xianxing)
        last_date = data_xianxing.index[-1]
        # 为了防止滞后一期或两期时丢失最新数据，要做序列延伸 
        for i in range(shift_num):
            cur_date = last_date
            next_date = Fs.get_next_n_month_last_date(cur_date,i+1)
            data_xianxing.loc[next_date] = np.nan 
        data_xianxing = data_xianxing.shift(shift_num)  
        # 因为做了滞后处理，所以横坐标的日期对应的数据是当日能拿到的最新数据
        # 比如0531这天对应的数据就是0531时能拿到的最新数据
        # 这个数据可能是4月的（滞后一期），也可能是3月的（滞后两期），也可能是当月的（日、周频）
        # 如果我在0519跑程序，跑出来的是0531的数据是nan，说明5月份截至19日还没有数据出来（可能是4月的数据或3月的数据）
        # 如果我在0519跑程序，跑出来的是0531的数据不是nan，说明5月份的数据已经出来了（可能是4月的数据或3月的数据，也可能是周、日频的5月的最新数据）
        data_xianxing = Fs.deal_inf(data_xianxing) # 如果进行了yoy那有可能出现inf值
        data_xianxing0 = Fs.deal_inf(data_xianxing0)
        
        return data_xianxing,data_xianxing0     
    
def get_filter_percent(df, windows,start_date,end_date):
    df_filter_percent=df.copy()
    df_filter_percent=df_filter_percent.truncate(start_date,end_date)
    df_filter_diff_percent=df.copy()
    df_filter_diff_percent=df_filter_diff_percent.truncate(start_date,end_date)

    start_loc = df.index.get_loc(start_date)
    end_loc = df.index.get_loc(end_date)
    for j in range(start_loc, end_loc+1):
        df_before_j = df.iloc[:j+1]
        if len(df_before_j)>=windows_hp: #历史数据超过windows_max
            df_filter_j=hpfilter(df.iloc[j-windows_hp+1:j+1],5)
            all_values1 = df_filter_j.values.flatten()
            all_values1 = all_values1[-windows*12:]
            df_filter_diff_j=df_filter_j.diff()
            all_values2 = df_filter_diff_j.values.flatten()
            all_values2 = all_values2[-windows*12:]
            all_values2 = all_values2[1:]
            df_filter_percent.iloc[j-start_loc]=stats.percentileofscore(all_values1, all_values1[-1])/100
            df_filter_diff_percent.iloc[j-start_loc]= stats.percentileofscore(all_values2, all_values2[-1])/100
        elif len(df_before_j)>=windows_min:#历史数据超过windows_min但不到windows_max
            df_filter_j=hpfilter(df.iloc[:j+1,0],5)
            all_values1 = df_filter_j.values.flatten()
            
            df_filter_diff_j=df_filter_j.diff()
            all_values2 = df_filter_diff_j.values.flatten()
            all_values2 = all_values2[1:]
            
            df_filter_percent.iloc[j-start_loc] = stats.percentileofscore(all_values1, all_values1[-1])/100
            df_filter_diff_percent.iloc[j-start_loc]= stats.percentileofscore(all_values2, all_values2[-1])/100
        else:
            df_filter_percent.iloc[j-start_loc]= np.nan
            df_filter_diff_percent.iloc[j-start_loc]= np.nan
    return df_filter_percent, df_filter_diff_percent

def infer_frequency(df):
    # 计算前两个时间点的差值来推断频率
    time_diff = df.index.to_series().diff().mode()[0]
    if time_diff == pd.Timedelta(days=1):
        return 'D'
    elif time_diff == pd.Timedelta(weeks=1):
        return 'W'
    elif pd.Timedelta(days=28) <= time_diff <= pd.Timedelta(days=31):
        return 'M'
    else:
        return None

def get_latest_processed_value(df):
    freq = infer_frequency(df)
    if freq == 'D':
        # 计算最新数据的滚动30天平均
        latest_value = df.rolling(window=30).mean().iloc[-1]
    elif freq == 'W':
        # 计算最新数据的滚动4周平均
        latest_value = df.rolling(window=4).mean().iloc[-1]
    return latest_value

# 将最新处理结果添加到数据框的2024-08对应行
def update_latest_value(df, latest_value):
    latest_date = pd.Timestamp('2024-08-31')
    df.loc[latest_date] = latest_value
    return df

def main(industry):

    folder='C:/Users/jzz/Desktop/全行业 - 副本/'
    #输入行业指标和万得全A

    industry1=industry+'2021年后'
    industry2=industry+'全样本'
    
    date_range = pd.date_range(start=start_date1, end=end_date1, freq='M')
    # 创建一个空的DataFrame，以日期范围为索引
    data_best = pd.DataFrame(index=date_range)
    #输入行业指标和万得全A
    target1 = pd.read_excel('C:/Users/jzz/Desktop/申万一级行业指数行情''.xlsx',index_col=0)
    target1=target1[industry]
    target1=pd.DataFrame(target1)
    
    table = pd.concat([target1.resample('m').last(),
                             target2.resample('m').last()],
                            axis=1).sort_index().dropna()#拼接2个dataframe，改成月频数据，按index排序，删除缺失值 
    table.columns = ['行业指数','万得全A']
    table['行业指数收益率'] = table['行业指数'].pct_change()  
    table['万得全A收益率'] = table['万得全A'].pct_change()  
    table = table.truncate(start_date1,end_date1)
    
    
    
    '最终参数选择，合并2021和2015之后的结果，这边这个结果也可以直接读入，但是因为我直接改代码，所以保留了这一部分'
    df_flag=pd.read_excel(folder+industry+'/'+industry+'指标信息.xlsx')
    df1=pd.read_excel(folder+industry+'/'+industry1+'/'+industry1+'方向选择及最优参数'+'.xlsx')
    df2=pd.read_excel(folder+industry+'/'+industry2+'/'+industry2+'方向选择及最优参数'+'.xlsx')
    df1 = pd.DataFrame(df1)
    df2 = pd.DataFrame(df2)
    df1 = df1[['代码', '指标', '形式','最后结论']].rename(columns={'最后结论': '2021-2024方向选择'})
    df2 = df2[['代码', '形式', '最后结论','是否滤波','最优窗口', '最优参数1', '最优参数2', '最优参数下绝对年化收益']].rename(columns={'最后结论': '全样本方向选择'})
    
    #将2个时间段的结果合并并输出
    merged_df = pd.merge(df1, df2, on=['代码', '形式'], how='outer')
    num=merged_df.shape[0]#指标总数
    merged_df['方向'] = merged_df.apply(merge_direction, axis=1)
    merged_df.to_excel(folder+industry+'/最终参数选择.xlsx', index=False)
    #absolute, relative_A, relative_industry,sharpe_ratio, max_drawdown
    columns1 = ['代码','名称','形式', '方向','是否带滤波','窗口','参数1','参数2',
                '策略收益率年化', '行业指数收益率年化', '万得全A收益率', '开始时间', '结束时间',
                '策略相对万得全A超额收益率年化','策略相对行业指数收益率年化',
                '绝对胜率','策略相对万得全A胜率', '策略相对行业指数胜率','夏普比率','最大回撤']
    data_new1 = pd.DataFrame(columns=columns1)
    
    #面积图分界点
    threshold = 0.5
    
    #每个指标最优参数回测并画图
    #调用数据库
    yunmacro = database_new.YunMacro()
    for i in range(0, num):
        #数据预处理
        code= merged_df['代码'].iloc[i]
        sql = "select end_dt, imvalue from macro_data where imcode='{}'".format(code)
        data = yunmacro.read_sql(sql)
        data['end_dt'] = pd.to_datetime(data['end_dt'])  # 转换为日期类型
        data.set_index('end_dt', inplace=True)
        direction=merged_df['方向'].iloc[i]
        form = merged_df['形式'].iloc[i]
        if direction =='舍去':
            continue
        else:
            #在df_flag里找对应指标index，用于滞后、同比、降频操作
            index_i = df_flag.index[df_flag['代码'] == code].tolist()[0]
            shift_num=df_flag['滞后期数'].iloc[index_i]
            need_yoy=df_flag['是否需同比操作'].iloc[index_i]
            is_w_d=df_flag['是否需要降频'].iloc[index_i]
            name=df_flag['指标名称'].iloc[index_i]
            #把不能放在文件名里的符号从指标名里删掉
            name = name.replace(":", "")
            name = name.replace("/", "")
            name = name.replace(">", "大于")
            name = name.replace("<", "小于")
            df,_=single_deal(data,shift_num,need_yoy,is_w_d)
            if infer_frequency(data)=='D' or infer_frequency(data)=='W':
                data_lastest=get_latest_processed_value(data)
                df = update_latest_value(df, data_lastest)
            df = df.ffill() #前向填充
            df=df.sort_index()
            
            #取得指标的开始和结束时间
            first_date=df.index[0]
            last_date=df.index[-1]
            if first_date <= pd.Timestamp(start_date1):
                start_date = start_date1
            else:
                start_date = first_date
            if last_date == pd.Timestamp(end_date1):
                end_date = end_date1
            else:
                end_date = last_date
            
            #根据最优参数处理数据
            is_filter=merged_df['是否滤波'].iloc[i]
            best_windows = int(merged_df['最优窗口'].iloc[i])
            best_x1 = merged_df['最优参数1'].iloc[i]
            best_x2 = merged_df['最优参数2'].iloc[i]
            if form =='原指标':
                if is_filter == '是':
                    best_df_percent, _= get_filter_percent(df,best_windows,start_date,end_date)
                else:
                    best_df_percent = get_percentile(best_windows,df,start_date,end_date)
            else:
                if is_filter == '是':
                    _, best_df_percent= get_filter_percent(df,best_windows,start_date,end_date)
                else:
                    df=df.diff()
                    best_df_percent = get_percentile(best_windows,df,start_date,end_date)
            best_df_percent = best_df_percent.truncate(start_date, end_date)
        
            #单指标最优参数回测
            score=get_stock_score(best_df_percent,best_x1,best_x2,direction)
            last_date_score = score.index[-1]
            # 检查最后一行日期是否是2024-07
            # 检查最后日期是否为2024-07-31或2024-06-30
            if last_date_score == pd.Timestamp('2024-07-31') or last_date_score == pd.Timestamp('2024-06-30'):
                # 检查'2024-08-31'是否在索引中
                if pd.Timestamp('2024-08-31') not in score.index:
                    # 增加一行'2024-08-31'，用'2024-07-31'的数据填充
                    score.loc[pd.Timestamp('2024-08-31')] = score.loc[last_date_score]

            winrate = score.copy()
            winrate[:]=1
            print('wind代码：',code)
    
            #把仓位和滚动6月胜率放入data_best
            score.columns = [code+name+form+'仓位']
            winrate.columns = [code+name+form+'相对胜率']
            data_best= data_best.join(score, how='left')
            data_best= data_best.join(winrate, how='left')
            
            
      
        
    '''等权加总'''
   
    #把所有的nan值都化成0（指标取nan说明这个时候没有回测数据，设成仓位为0）
    data_best = data_best.fillna(0)
    # 获取所有得分列的位置索引
    winrate_columns = [col for col in data_best.columns if '相对胜率' in col]
    #把仓位和胜率合并在一张表，并计算权重
    data_best['总胜率'] = data_best[winrate_columns].sum(axis=1)
    new_columns = []
    weighted_sums = pd.Series(0, index=data_best.index)
    for i in range(0, len(data_best.columns) - 1, 2):
        new_columns.append(data_best.columns[i])  # 插入指标列
        new_columns.append(data_best.columns[i + 1])  # 插入得分列
        weight_col = f'{data_best.columns[i + 1][0:]}权重'  # 创建权重列名
        data_best[weight_col] = np.where(
        data_best['总胜率'] == 0, 
        0, 
        data_best[data_best.columns[i + 1]] / data_best['总胜率']#如果所有胜率都是0，那就把仓位设为0，否则无法加权
    )
        new_columns.append(weight_col)  # 插入权重列
        # 计算加权后的仓位
        weighted_sums += data_best[data_best.columns[i]] * data_best[weight_col]
        data_best['加权后总仓位'] = weighted_sums
    # 按新顺序排列列
    data_best = data_best[new_columns]
    
    table=table.iloc[1:]#滚动6月胜率，删去前6期
    weighted_sums.to_excel(folder+industry+'/加权仓位.xlsx', index=True)
    data_best.to_excel(folder+industry+'/全指标仓位.xlsx', index=True)
    


#%% 主程序
start_date1 = '2015-12-31'
end_date1 = '2024-10-31'
windows_year = [1,3,5] 
windows_min = 12*min(windows_year)
windows_hp = 12*5


yunwind = database_new.YunWind()
sql2 = '''Select TRADE_DT,S_DQ_CLOSE from AINDEXWINDINDUSTRIESEOD 
where S_INFO_WINDCODE='881001.WI' '''
target2 = yunwind.read_sql(sql2)
target2['TRADE_DT'] = pd.to_datetime(target2['TRADE_DT'])  # 转换为日期类型
target2.set_index('TRADE_DT', inplace=True)

#

industry_values = ['通信','房地产','公用事业',
              '家用电器','建筑材料','国防军工','传媒','纺织服饰',
             '机械设备','有色金属','基础化工','钢铁',
             '电力设备','电子','农林牧渔','建筑装饰',
              '煤炭','轻工制造','医药生物',
              '计算机','交通运输','美容护理','汽车',
              '商贸零售','食品饮料','社会服务','石油石化']



# 循环运行
for industry in industry_values:
    main(industry)
    print(industry)
    
    
def find_and_extract_columns(root_dir, target_filename, column_name):
    # 用来存储读取的列数据
    dataframes = []

    # 遍历根目录下的所有子文件夹和文件
    for subdir, _, files in os.walk(root_dir):
        for file in files:
            if file == target_filename:
                file_path = os.path.join(subdir, file)
                folder_name = os.path.basename(subdir)  # 获取文件夹的名称
                # 读取Excel文件
                print(1)
                df = pd.read_excel(file_path, index_col=0) 
                # 提取指定的列并重命名
                if column_name in df.columns:
                    extracted_column = df[[column_name]].copy()
                    extracted_column.columns = [folder_name]  # 将列名改为文件夹名称
                    dataframes.append(extracted_column)
                    print(f'Extracted column from file: {file_path} as {folder_name}')

    # 将所有提取的列拼接在一起
    if dataframes:
        combined_df = pd.concat(dataframes, axis=1)
        return combined_df
    else:
        return None
folder='C:/Users/jzz/Desktop/全行业 - 副本/'
# 设定根目录
root_directory = folder[:-1]
#'择时指标得分'是当月得分，用来择时要下一个月，'是否实际持有'是用来择时的仓位，也就是'择时指标得分'滞后一期
result_df1 = find_and_extract_columns(root_directory, '加权仓位.xlsx', 0)
result_df1.to_excel(folder+'加权仓位.xlsx')
    
